﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace Models
{
    static public class CompareDatabaseModel
    {
        static public string Table_Define = @"TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE";
        static public string Table_Index = @"table_name,index_name,column_name,key_ordinal,type_desc,is_unique,is_primary_key";
        static public string Table_Identity = @"table_name,column_name,data_type,max_length,seed_value,increment_value";
        static public DataTable GetAllDatabase(string server, bool Authentication, string catalog, string login, string password) 
        {
            DataTable tb = new DataTable();
            DataModel conn = new DataModel(server, Authentication, catalog, login, password);
            if(!conn.CheckConnection())
                return tb;
            try
            {
                tb = conn.QueryResults(@"SELECT *
                                            FROM master.dbo.sysdatabases").Tables[0];
            }
            catch
            {
                return new DataTable();
            }
            return tb;
        }
        
        //GetAllTableDefine
        static public DataTable GetTableInfo(DataModel conn,int type) //0:tableDefine 1:TableIndex 2: TableIndentity
        {
            DataTable tb = new DataTable();
            string query = string.Empty;
            switch(type)
            {                
                case 1:
                    query = FilesModel.RealAllText("Table_Index.sql");                    
                    break;
                case 2:
                    query = FilesModel.RealAllText("Table_Identity.sql");
                    break;
                default:
                    query = "SELECT " + Table_Define;
                    query += @" FROM INFORMATION_SCHEMA.COLUMNS 
                                WHERE TABLE_NAME IN (select name from sys.objects where type = 'U')";
                    break;
            }
            if (string.IsNullOrEmpty(query))
                return tb;
            try
            {
                tb = conn.QueryResults(string.Format(query, conn.conn.Database)).Tables[0];
            }
            catch
            {
                //System.Windows.MessageBox.Show(ex.Message, "");
                return new DataTable();
            }
            return tb;
        }


        //So khớp 2 DataRow
        static private bool MatchRow(DataRow row1, DataRow row2,string columns)
        {
            string[] ArrColumn = columns.Split(',');
            foreach (string cl in ArrColumn)
                if (!row1[cl].Equals(row2[cl]))
                    return false;
            return true;
        }

        //So sách 2 Table
        static public DataTable CompareTable(DataTable tb1, DataTable tb2,string columns,int type)
        {
            DataTable tbResult = tb1.Copy();
            tbResult.Rows.Clear();
            tbResult.Columns.Add("NEW");
            DataRow[] rows = null;
            string[] key = columns.Split(',');
            foreach (DataRow row in tb1.Rows)
            {
                string condition = key[0] + " = '" + row[key[0]] + "' and " + key[1] + " = '" + row[key[1]] + "'";
                if (type == 1)
                    condition += " and " + key[2] + " = '" + row[key[2]] + "'";
                try
                {
                    rows = tb2.Select(condition);
                }
                catch
                {
                    continue;
                }
                if (rows.Length > 0)
                {
                    //if (row["table_name"].ToString().Contains("x_ph51"))
                    //    tb1 = tb1;
                    if (!MatchRow(rows[0], row, columns))
                    {
                        tbResult.Rows.Add(row.ItemArray);
                        tbResult.Rows[tbResult.Rows.Count - 1]["NEW"] = "2";
                    }
                    else
                        continue;
                }
                else
                {
                    try
                    {
                        tbResult.Rows.Add(row.ItemArray);
                        if(tb2.Select(key[0] + " = '" + row[key[0]] + "'").Length <=0)
                            tbResult.Rows[tbResult.Rows.Count - 1]["NEW"] = "0";
                        else
                            tbResult.Rows[tbResult.Rows.Count - 1]["NEW"] = "1";
                    }
                    catch
                    {
                        continue;
                    }
                }
            }
            return tbResult;
        }

        //Get all procedure
        
    }
}
